# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html

from scrapy.conf import settings
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:root@localhost/db_mt2?charset=utf8', echo=True)
Base = declarative_base()

class Deals(Base):
    """套餐表"""

    __tablename__ = 'deals'

    id = Column(Integer, primary_key=True, autoincrement=True)
    dealid = Column(Integer)
    title = Column(String(50))  # 套餐名称
    price = Column(Float)   # 价格
    valus = Column(Float)   # 原价
    sales = Column(Integer)   # 销售量
    shop_id = Column(Integer, ForeignKey('shop.id')) # 外键


    def __repr__(self):
        return "<deals(title='{}', price='{}', value='{}', sales='{}', shopid='{}')>".format(
            self.title, self.price, self.value, self.sales, self.shopid)


class Shop(Base):
    """店铺表"""

    __tablename__ = 'shop'  # 表名

    # id = Column(Integer, primary_key=True, autoincrement=True)
    id = Column(Integer, primary_key=True, autoincrement=True)
    shopid = Column(Integer)  # 店铺id
    cateId = Column(String(10)) # 类别
    title = Column(String(30))  # 店铺名
    address = Column(String(100))    # 店铺地址
    lowestprice = Column(Float) # 最低消费
    avgprice = Column(Float)    # 人均消费
    latitude = Column(String(50))    # 纬度
    longitude = Column(String(50))   # 经度
    avgscore = Column(String(20))    # 评分
    comments = Column(Integer)  # 评论数
    historyCouponCount = Column(Integer)  # 消费人数
    areaname = Column(String(20))   # 区域

    def __repr__(self):
        return "<shop(shopid='{}',cateId='{}',title='{}',address='{}',lowestprice='{}',avgprice='{}',latitude='{}'," \
               "longitude='{}',avgscore='{}',comments='{}',historyCouponCount='{}',areaname='{}')>".format(self.shopid,
                self.cateId, self.title, self.address, self.lowestprice, self.avgprice, self.latitude, self.longitude,
                self.avgscore, self.comments, self.historyCouponCount, self.areaname)


Base.metadata.create_all(engine)





class MeituanwangPipeline(object):
    def __init__(self):
        self.host = settings['MYSQL_HOST']
        self.user = settings['MYSQL_USER']
        self.passwd = settings['MYSQL_PASSWORD']
        self.db = settings['MYSQL_DB']
        self.charset = settings['MYSQL_CHARTSET']
        self.Session = sessionmaker(bind=engine)


    def process_item(self, item, spider):
        session = self.Session()
        info = dict(item)
        shop = Shop(shopid=info['shopid'], cateId=info['cateId'], title=info['title'], address=info['address'],
                    lowestprice=info['lowestprice'], avgprice=info['avgprice'], latitude=info['latitude'],
                    longitude=info['longitude'], avgscore=info['avgscore'], comments=info['comments'],
                    historyCouponCount=info['historyCouponCount'], areaname=info['areaname'])
        session.add(shop)
        session.commit()

        for tg in info['deals']:
            deals = Deals(id=tg['id'], title=tg['title'], price=tg['price'], value=tg['value'], sales=tg['sales'],
                          shopid=info['shopid'])
            session.add(deals)
            session.commit()

        session.close()
        return item